package com.newcapec.news.dao.impl;


import com.newcapec.news.dao.SysUserDao;
import com.newcapec.news.entity.SysUser;
import com.newcapec.news.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class SysUserDaoImpl implements SysUserDao {



    @Override
    public void addUser(SysUser sysUser) {
        Connection conn=null;
        PreparedStatement stmt=null;
        try {
            conn = JDBCUtil.getConn();
            String sql="insert into sys_user (name,acct,pwd,crtime,uptime) values(?,?,?,now(),now())";
            stmt=conn.prepareStatement(sql);
            stmt.setString(1,sysUser.getName());
            stmt.setString(2,sysUser.getAcct());
            stmt.setString(3,sysUser.getPwd());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,stmt,null);
        }
    }

    @Override
    public void updateUser(SysUser sysUser) {

        Connection conn=null;
        PreparedStatement stmt=null;
        try {
            conn = JDBCUtil.getConn();
           // String sql="insert into sys_user (name,acct,pwd,crtime,uptime) values(?,?,?,now(),now())";
            String sql="update sys_user set name=? ,pwd = ?  ,crtime = now() where id = ?";
            stmt=conn.prepareStatement(sql);
            stmt.setString(1,sysUser.getName());
            stmt.setString(2,sysUser.getPwd());
            stmt.setInt(3,sysUser.getId());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,stmt,null);
        }

    }

    @Override
    public void delUser(int id) {

        Connection conn=null;
        PreparedStatement stmt=null;
        try {
            conn = JDBCUtil.getConn();
            String sql="delete from sys_user where id= ?";
            stmt=conn.prepareStatement(sql);
            stmt.setInt(1,id);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,stmt,null);
        }

    }

    @Override
    public List<SysUser> queryAll() {
        List<SysUser> list=new ArrayList<>();
        Connection conn=null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            conn = JDBCUtil.getConn();
            String sql="select id,name,acct,pwd,crtime,uptime from sys_user";
            stmt=conn.prepareStatement(sql);
            rs=stmt.executeQuery();
            while(rs.next()){
                SysUser sysUser=new SysUser();
                sysUser.setId(rs.getInt("id"));
                sysUser.setName(rs.getString("name"));
                sysUser.setAcct(rs.getString("acct"));
                sysUser.setPwd(rs.getString("pwd"));
                sysUser.setCrtime(rs.getString("crtime"));
                sysUser.setUptime(rs.getDate("uptime"));
                list.add(sysUser);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,stmt,rs);
        }
        return list;
    }

    @Override
    public List<SysUser> queryAll(int offset, int pageSize, SysUser user) {
        List<SysUser> list=new ArrayList<>();
        Connection conn=null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            conn = JDBCUtil.getConn();
            String sql="select id,name,acct,pwd,crtime,uptime from sys_user";
            if(user!=null && user.getName() !=null){
                sql+="where name like ?";
            }
               sql+=" limit ?,?";

            stmt=conn.prepareStatement(sql);

            if(user!=null && user.getName() !=null){
                stmt.setString(1,"%"+user.getName()+"%");
                stmt.setInt(2,offset);
                stmt.setInt(3,pageSize);
            }else{
                stmt.setInt(1,offset);
                stmt.setInt(2,pageSize);
            }


            rs=stmt.executeQuery();
            while(rs.next()){
                SysUser sysUser=new SysUser();
                sysUser.setId(rs.getInt("id"));
                sysUser.setName(rs.getString("name"));
                sysUser.setAcct(rs.getString("acct"));
                sysUser.setPwd(rs.getString("pwd"));
                sysUser.setCrtime(rs.getString("crtime"));
                sysUser.setUptime(rs.getDate("uptime"));
                list.add(sysUser);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,stmt,rs);
        }
        return list;
    }

    @Override
    public SysUser getUserById(int id) {
        SysUser sysUser=null;
        Connection conn=null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            conn = JDBCUtil.getConn();
            String sql="select id,name,acct,pwd,crtime,uptime from sys_user where id=?";

            stmt=conn.prepareStatement(sql);

            stmt.setInt(1,id);
            rs=stmt.executeQuery();
            while(rs.next()){
                 sysUser=new SysUser();
                sysUser.setId(rs.getInt("id"));
                sysUser.setName(rs.getString("name"));
                sysUser.setAcct(rs.getString("acct"));
                sysUser.setPwd(rs.getString("pwd"));
                sysUser.setCrtime(rs.getString("crtime"));
                sysUser.setUptime(rs.getDate("uptime"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,stmt,rs);
        }
        return sysUser;
    }

    @Override
    public SysUser getUserByAcct(String acct) {
        SysUser user=null;
        Connection conn=null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            conn = JDBCUtil.getConn();
            String sql="select id,name,acct,pwd,crtime,uptime from sys_user where acct =?";
            stmt=conn.prepareStatement(sql);
           stmt.setString(1,acct);
            rs=stmt.executeQuery();
            while(rs.next()){
                user =new SysUser();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setAcct(rs.getString("acct"));
                user.setPwd(rs.getString("pwd"));
                user.setCrtime(rs.getString("crtime"));
                user.setUptime(rs.getDate("uptime"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,stmt,rs);
        }
        return user;
    }

    @Override
    public List<Map<String, String>> queryUser() {
        String sql="select id,name,acct,pwd,crtime,uptime from sys_user";
        return  JDBCUtil.query(sql,new Object[]{});

    }

    @Override
    public int getAllCont() {
        Connection conn=null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            conn = JDBCUtil.getConn();
            String sql="select count(1)  from sys_user ";
            stmt=conn.prepareStatement(sql);

            rs=stmt.executeQuery();
            if(rs.next()){
               return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(conn,stmt,rs);
        }
        return 0;
    }


}
